using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System;

namespace Util {

	public class DataProvider 
    {
		public DataProvider(string connectionString) 
        {
			this.connectionString = connectionString;
			this.connection = new SqlConnection(connectionString);
		}

		private string connectionString;
		public string ConnectionString 
        {
			get { return connectionString; }
			set { connectionString = value; }
		}

		private SqlConnection connection;
        
        public SqlTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();

            SqlTransaction trans = conn.BeginTransaction(isolationLevel);
            return trans;
        }

        public void CommitTransaction(SqlTransaction trans)
        {
            SqlConnection conn = trans.Connection;
            trans.Commit();
            conn.Close();            
        }

        public void RollbackTransaction(SqlTransaction trans)
        {
            try
            {
                SqlConnection conn = trans.Connection;
                trans.Rollback();
                conn.Close();
            }catch(Exception ex)
            {
                //ToDo log
            }
        }

		public SqlCommand PrepareCommand(string commandText, CommandType commandType) {
			return PrepareCommand(commandText, commandType, null);
		}

		public SqlCommand PrepareCommand(string commandText, CommandType commandType, 
			SqlTransaction trans)
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = commandText;
			command.CommandType = commandType;

            if (trans != null)
            {
                command.Connection = trans.Connection;
                command.Transaction = trans;
            }
            else
            {
                command.Connection = new SqlConnection(connectionString);
            }

			return command;
		}

		public object ExecuteScalar(SqlCommand command) {
			try {
                if (command.Connection.State != ConnectionState.Open)
				    command.Connection.Open();

				return command.ExecuteScalar();
			} 
			finally {
                if (command.Transaction == null)
				    command.Connection.Close();
			}
		}

		public int ExecuteNonQuery(SqlCommand command) {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                return command.ExecuteNonQuery();
            }
            finally
            {
                if (command.Transaction == null)
                    command.Connection.Close();
            }
		}

        public DataSet ExecuteDataSet(string commandName, CommandType commandType, SqlParameter[] parameters, params string[] tableName)
        {
            SqlCommand cmd = PrepareCommand(commandName, commandType);

            foreach (SqlParameter param in parameters)
                cmd.Parameters.Add(param);

            return ExecuteDataSet(cmd, tableName);
        }

		public DataSet ExecuteDataSet(SqlCommand command) { return ExecuteDataSet(command, "Table"); }

		public DataSet ExecuteDataSet(SqlCommand command, params string[] tableName) {
			using(SqlDataAdapter adapter = new SqlDataAdapter(command)) {
				DataSet dsData = new DataSet();
				
                adapter.Fill(dsData);

                if (dsData.Tables.Count > 0)
                {
                    for (int i = 0 ; i< dsData.Tables.Count ; i++)
                        dsData.Tables[i].TableName = tableName[i];
                }
                else
                    dsData.Tables.Add(tableName[0]);

				return dsData;
			}
		}

        public void FillDataTable(SqlCommand command, DataTable dtToFill)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                adapter.Fill(dtToFill);
        }

        #region Util methods
        public DataSet SelectWholeDataTable(string tableName)
        {
            return SelectWholeDataTable(tableName, null, null);
        }

        public DataSet SelectWholeDataTable(string tableName, string criteria, string criteriaValue)
        {
            if (tableName.Split(' ').Length > 1)
                throw new Exception("Invalid query");

            string query = "SELECT * FROM {0} {1}";

            if (criteria == null)
                query = string.Format(query, tableName, "");
            else
            {
                // checking for possible invalid queries like SELECT * FROM tableName WHERE a = _'abc'; SELECT * FROM systable_
                // TODO: need further revision
                if (criteriaValue.StartsWith("'"))
                {
                    // it's nvarchar value, and it must be formatted in way 'value'
                    int indexOfEnd = criteriaValue.IndexOf("'", 1);
                    if (indexOfEnd > criteriaValue.Length - 1)
                        throw new Exception("Invalid query");
                }
                else
                {
                    // bool, int or some other similar value... no spaces allowed
                    if (criteriaValue.IndexOf(" ") > -1)
                        throw new Exception("Invalid query");
                }

                query = string.Format(query, tableName,
                    string.Format("WHERE ({0} = {1})", criteria, criteriaValue));
            }

            SqlCommand cmd = PrepareCommand(query, CommandType.Text);

            return ExecuteDataSet(cmd, tableName.Split(' ')[0]);
        }

        public DataSet SaveTableUpdate(DataSet dsData)
        {
            foreach (DataTable dt in dsData.Tables)
                SaveTableUpdate(dt);

            return dsData;
        }

        public DataSet SaveTableUpdate(DataSet dsData, params string[] tableNames)
        {
            foreach (string s in tableNames)
            {
                if (dsData.Tables.Contains(s))
                    SaveTableUpdate(dsData.Tables[s]);
            }

            return dsData;
        }

        public DataTable SaveTableUpdate(DataTable dtSaving)
        {
            SqlCommand cmd = PrepareCommand("SELECT * FROM " + dtSaving.TableName, CommandType.Text);

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
                adapter.Update(dtSaving);
            }

            return dtSaving;
        }

        #endregion
    }
}